#include "attendancequery.h"
#include "databasemanager.h"

#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>

AttendanceQuery::AttendanceQuery(QSqlDatabase db, QObject *parent)
    : QObject{parent}
    , m_db{db}
{


}

QSqlQuery AttendanceQuery::setQuery(const QString &timeRange, const QString &dept, const QString &status, const QDate &date)
{
    QSqlQuery query(m_db);
    QStringList conditions;
    QHash<QString, QVariant> bindValues;

    // 处理时间范围条件（基于传入的日期）
    if (timeRange != "所有") {
        if (!date.isValid()) {
            qWarning() << "时间范围需要传入有效日期!";
            return QSqlQuery();
        }

        if (timeRange == "月考勤表") {
            conditions.append("strftime('%Y-%m', a.check_date) = strftime('%Y-%m', :date_param)");
        } else if (timeRange == "周考勤表") {
            conditions.append("strftime('%Y-%W', a.check_date) = strftime('%Y-%W', :date_param)");
        } else if (timeRange == "日考勤表") {
            conditions.append("a.check_date = :date_param");
        }
        bindValues[":date_param"] = date.toString("yyyy-MM-dd");
    }

    // 处理部门条件
    if (dept != "所有部门") {
        conditions.append("d.dept_name = :dept_name");
        bindValues[":dept_name"] = dept;
    }

    // 处理状态条件
    if (status != "所有") {
        if (status == "正常") {
            conditions.append("a.status = '正常'");
        } else if (status == "异常") {
            conditions.append("a.status IN ('迟到', '早退', '缺勤')");
        }
    }

    // // 处理指定日期
    // if (date.isValid()) {
    //     conditions.append("a.check_date = :check_date");
    //     bindValues[":check_date"] = date.toString("yyyy-MM-dd");
    // }

    // 构建完整SQL
    QString sql = "SELECT a.att_id AS '考勤号', "
                  " a.check_date AS '日期', "
                  " a.clock_in AS '上班打卡', "
                  " a.clock_out AS '下班打卡', "
                  " a.status AS '状态', "
                  " a.emp_id AS '员工号', "
                  " e.emp_name AS '员工姓名',"
                  " d.dept_name AS '部门名' "
                  "FROM attendance a "
                  "JOIN employee e ON a.emp_id = e.emp_id "
                  "JOIN department d ON e.dept_id = d.dept_id";
    if (!conditions.isEmpty()) {
        sql += " WHERE " + conditions.join(" AND ");
    }

    // qDebug() << "sql:" << sql;
    // qDebug() << "参数:" << bindValues;

    // 绑定参数并执行
    query.prepare(sql);
    for (auto it = bindValues.constBegin(); it != bindValues.constEnd(); ++it) {
        query.bindValue(it.key(), it.value());
    }

    if (!query.exec()) {
        qWarning() << "setQuery<Query failed>:" << query.lastError().text();
        // return {};
    }

    // // 解析结果到结构体
    // QVector<AttendanceRecord> records;
    // while (query.next()) {
    //     AttendanceRecord rec;
    //     rec.att_id = query.value("att_id").toInt();
    //     rec.emp_name = query.value("emp_name").toString();
    //     rec.dept_name = query.value("dept_name").toString();
    //     // 其他字段类似...
    //     records.append(rec);

    //     qDebug() << "考勤号:" << rec.att_id;
    //     qDebug() << "员工姓名:" << rec.emp_name;
    //     qDebug() << "部门名:" << rec.dept_name;
    // }
    return query;
}

QSqlQuery AttendanceQuery::queryDeptAttendance(const QString &deptName, const QString &granularity, const QDate &date)
{
    Q_UNUSED(deptName);

    QString sql;

    if (granularity == "日考勤表") {
        sql = R"(
            -- 日粒度查询（直接统计attendance表）
            SELECT
                d.dept_id,
                d.dept_name,
                COUNT(CASE WHEN a.status='正常' THEN 1 END) AS normal_days,
                COUNT(*) AS total_days,
                ROUND(COUNT(CASE WHEN a.status='正常' THEN 1 END)*100.0/COUNT(*),2) AS rate
            FROM attendance a
            JOIN employee e ON a.emp_id = e.emp_id
            JOIN department d ON e.dept_id = d.dept_id
            WHERE DATE(a.check_date) = DATE(:date_param)
            GROUP BY d.dept_id
            )";
    } else if (granularity == "周考勤表") {
        sql = R"(
            -- 周粒度查询（利用视图v_dept_weekly_attendance）
            SELECT
                dept_id,
                dept_name,
                week,
                SUM(normal_days) AS normal_days,
                SUM(total_days) AS total_days,
                ROUND(SUM(normal_days)*100.0/SUM(total_days),2) AS rate
            FROM v_dept_weekly_attendance
            WHERE week = strftime('%Y-%W', :date_param)
            GROUP BY dept_id
            )";
    } else if (granularity == "月考勤表") {
        sql = R"(
            -- 月粒度查询（利用视图v_employee_monthly_attendance聚合）
            SELECT
                d.dept_id,
                d.dept_name,
                strftime('%Y-%m', a.check_date) AS month,
                COUNT(CASE WHEN a.status='正常' THEN 1 END) AS normal_days,
                COUNT(*) AS total_days,
                ROUND(COUNT(CASE WHEN a.status='正常' THEN 1 END)*100.0/COUNT(*),2) AS rate
            FROM attendance a
            JOIN employee e ON a.emp_id = e.emp_id
            JOIN department d ON e.dept_id = d.dept_id
            WHERE strftime('%Y-%m', a.check_date) = strftime('%Y-%m', :date_param)
            GROUP BY d.dept_id
            )";
    } else {
        qDebug() << "Invalid granularity : " << granularity;
        return QSqlQuery();
    }

    QSqlQuery query(m_db);

    query.prepare(sql);
    // query.bindValue(":dept_id", DatabaseManager::getDepartmentId(deptName));
    query.bindValue(":date_param", date.toString("yyyy-MM-dd"));

    if (!query.exec()) {
        qDebug() << "queryDeptAttendance<Query failed>: " + query.lastError().text();
        return QSqlQuery();
    }


    return query;
}
